﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Wei8808Cms.Model;
using Wei8808Cms.Utility;

namespace Wei8808Cms.DAL
{
    public class AdministratorDAL : DbHelperBase
    {
        public int GetCount(Pagination qcmodel)
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*) AS [Total] FROM Administrator WHERE 1=1 ");
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public DataSet GetPageList(Pagination qcmodel)
        {
            var ds = new DataSet();
            var strSql = new StringBuilder();
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by CreateTime) AS idx,[Id],[UserName],[UserPwd],[ReadName],[UserEmail],[UserType],[UserLevel],[IsLock] FROM [Administrator] ");
            strSql.Append(" WHERE 1=1 ");
            strSql.Append(" ) AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    ds = Db.ExecuteDataSet(cmd);
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return ds;
        }

        public bool Add(AdministratorInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [Administrator]([Id],[UserName],[UserPwd],[ReadName],[UserEmail],[UserType],[UserLevel],[IsLock],[CreateUser],[CreateTime]) ");
            strSql.Append(" VALUES(@Id,@UserName,@UserPwd,@ReadName,@UserEmail,@UserType,@UserLevel,@IsLock,@CreateUser,@CreateTime) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@Id", model.Id);
                    Db.AddInParameter(cmd, "@UserName", model.UserName);
                    Db.AddInParameter(cmd, "@UserPwd", EncryptPassWord.EncryptString(model.UserPwd));
                    Db.AddInParameter(cmd, "@ReadName", model.ReadName);
                    Db.AddInParameter(cmd, "@UserEmail", model.UserEmail);
                    Db.AddInParameter(cmd, "@UserType", model.UserType);
                    Db.AddInParameter(cmd, "@UserLevel", model.UserLevel);
                    Db.AddInParameter(cmd, "@IsLock", model.IsLock);
                    Db.AddInParameter(cmd, "@CreateUser", model.CreateUser);
                    Db.AddInParameter(cmd, "@CreateTime", model.CreateTime);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool Delete(List<AdministratorInfo> list)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [Administrator] WHERE Id=@Id ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@Id", DbType.String);
                    foreach (var i in list)
                    {
                        cmd.Parameters["@Id"].Value = i.Id;
                        Db.ExecuteNonQuery(cmd);
                    }
                    result = true;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public AdministratorInfo GetAdministratorInfo(string id)
        {
            var model = new AdministratorInfo();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [Id],[UserName],[UserPwd],[ReadName],[UserEmail],[UserType],[UserLevel],[IsLock] FROM [Administrator] where Id=@Id ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@Id", id);
                    using (var reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            model.Id = reader["Id"] == obj ? null : Convert.ToString(reader["Id"]);
                            model.UserName = reader["UserName"] == obj ? null : Convert.ToString(reader["UserName"]);
                            model.UserPwd = reader["UserPwd"] == obj ? null : Convert.ToString(reader["UserPwd"]);
                            model.ReadName = reader["ReadName"] == obj ? null : Convert.ToString(reader["ReadName"]);
                            model.UserEmail = reader["UserEmail"] == obj ? null : Convert.ToString(reader["UserEmail"]);
                            model.UserLevel = reader["UserLevel"] == obj ? null : Convert.ToString(reader["UserLevel"]);
                            if (reader["UserType"] != DBNull.Value)
                                model.UserType = Convert.ToInt32(reader["UserType"]);
                            if (reader["IsLock"] != DBNull.Value)
                                model.IsLock = Convert.ToInt32(reader["IsLock"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public bool Update(AdministratorInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [Administrator] SET [UserName] = @UserName,[UserPwd] = @UserPwd,[ReadName] = @ReadName,[UserEmail] = @UserEmail,[UserType] = @UserType,[UserLevel] = @UserLevel,[IsLock] = @IsLock,[ModifyUser] = @ModifyUser,[ModifyTime] = @ModifyTime WHERE [Id] = @Id ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@UserName", model.UserName);
                    Db.AddInParameter(cmd, "@UserPwd", EncryptPassWord.EncryptString(model.UserPwd));
                    Db.AddInParameter(cmd, "@ReadName", model.ReadName);
                    Db.AddInParameter(cmd, "@UserEmail", model.UserEmail);
                    Db.AddInParameter(cmd, "@UserType", model.UserType);
                    Db.AddInParameter(cmd, "@UserLevel", model.UserLevel);
                    Db.AddInParameter(cmd, "@IsLock", model.IsLock);
                    Db.AddInParameter(cmd, "@ModifyUser", model.ModifyUser);
                    Db.AddInParameter(cmd, "@ModifyTime", model.ModifyTime);
                    Db.AddInParameter(cmd, "@Id", model.Id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool Exists(string userName)
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT count(1) AS [Total] FROM Administrator WHERE UserName=@UserName ");
            var result = false;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@UserName", userName);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (Convert.ToInt32(reader["Total"]) > 0)
                            {
                                result = true;
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return result;
            }
        }
    }
}
